﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace CTManagerSoln
{
    public class User
    {
        internal static int Insert(string UserName, string PassWord, string Email, string FullName, string Birthday, string Position, int isLogin, int isDelete)
        {
            int stateUpdate;            
            //string sql = "INSERT into tblUserNew (UserName,PassWord,Email,FullName,Birthday,Position,isAdmin,isLogin,isDelete) Values(@username,@pass,@email,@fullname,@birthday,@position,@isAdmin,@isLogin,@isDelete)";
            string sql = "INSERT INTO tblUsers (UserName,PassWord) Values(@username,@pass)";
            using (OleDbConnection conn = new OleDbConnection(Commons.DBConnect.ConnectionString))
            {
                using (OleDbCommand command = new OleDbCommand(sql, conn))
                {
                    conn.Open();                    
                    command.Parameters.Add("@username", OleDbType.VarChar).Value = UserName;
                    command.Parameters.Add("@pass", OleDbType.VarChar).Value = PassWord;
                    //command.Parameters.Add("@email", OleDbType.VarChar).Value = Email;
                    //command.Parameters.Add("@fullname", OleDbType.VarChar).Value = FullName;
                    //command.Parameters.Add("@birthday", OleDbType.VarChar).Value = Birthday;
                    //command.Parameters.Add("@position", OleDbType.VarChar).Value = Position;
                    //command.Parameters.Add("@isAdmin", OleDbType.Boolean).Value = false;
                    //command.Parameters.Add("@islogin", OleDbType.Boolean).Value = false;
                    //command.Parameters.Add("@isdelete", OleDbType.Boolean).Value = false;
                    stateUpdate = command.ExecuteNonQuery();
                }
            }
            return stateUpdate;
        }

        internal static DataTable GetallUser()
        {
            DataTable dtReturn = new DataTable();
            string selectString =string.Format("SELECT UserName,FullName FROM tblUsers where isDelete={0}",false);
            OleDbCommand cmd = new OleDbCommand(selectString, Commons.DBConnect);
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
            adapter.Fill(dtReturn);
            return dtReturn;
        }

        internal static DataTable getUser(string username, string pass, bool isdelete)
        {
            DataTable dtReturn = new DataTable();
            //string selectString = string.Format("SELECT UserName,PassWord,Email,FullName,Birthday,Position,isLogin FROM tblUsers where UserName='{0}' and PassWord='{1}' and isDelete={2};", username, pass, isdelete);
            string selectString = "SELECT UserName,PassWord,isAdmin FROM tblUsers where UserName=@UserName and PassWord=@PassWord and isDelete=@isDelete";
            using (OleDbConnection conn = new OleDbConnection(Commons.DBConnect.ConnectionString))
            {
                using (OleDbCommand command = new OleDbCommand(selectString, conn))
                {
                    conn.Open();
                    command.Parameters.Add("@UserName", OleDbType.VarChar).Value = username;
                    command.Parameters.Add("@PassWord", OleDbType.VarChar).Value = pass;
                    command.Parameters.Add("@isDelete", OleDbType.Boolean).Value = isdelete;
                    OleDbDataAdapter adapter = new OleDbDataAdapter(command);
                    adapter.Fill(dtReturn);
                }
            }
            return dtReturn;
        }

        internal static int inSerts(string userName, string pass, string email, string fullname, string _datebirthday, string position, int p, int p_2)
        {
            int stateUpdate=0;
            string sql = string.Format("INSERT into tblUsers (UserNames,PassWords) Values('{0}','{1}');", userName, pass);

            using (OleDbConnection conn = new OleDbConnection(Commons.DBConnect.ConnectionString))
            {
                using (OleDbCommand command = new OleDbCommand(sql, conn))
                {
                    conn.Open();
                    //command.Parameters.Add("@username", OleDbType.VarChar).Value = userName;
                    //command.Parameters.Add("@pass", OleDbType.VarChar).Value = pass;
                   stateUpdate= command.ExecuteNonQuery();
                }
            }
            return 1;
        }

        internal static DataTable getUser(string username, string hovaten)
        {
            DataTable dtReturn = new DataTable();
            //string selectString = string.Format("SELECT UserName,PassWord,Email,FullName,Birthday,Position,isLogin FROM tblUsers where UserName='{0}' and PassWord='{1}' and isDelete={2};", username, pass, isdelete);
            string selectString=string.Empty;
            if (username=="" && hovaten=="")
            {
                selectString = "SELECT * FROM tblUsers where isDelete=false and UserName<>'admin'";
            }
            else
            {
                selectString = string.Format("SELECT * FROM tblUsers where isDelete=false and UserName!='admin' and UserName='{0}' or FullName='{1}'", username, hovaten);
            }
            using (OleDbConnection conn = new OleDbConnection(Commons.DBConnect.ConnectionString))
            {
                using (OleDbCommand command = new OleDbCommand(selectString, conn))
                {
                    conn.Open();
                    //command.Parameters.Add("@UserName", OleDbType.VarChar).Value = username;
                    //command.Parameters.Add("@hovaten", OleDbType.VarChar).Value = hovaten;
                    OleDbDataAdapter adapter = new OleDbDataAdapter(command);
                    adapter.Fill(dtReturn);
                }
            }
            return dtReturn;
        }
    }
}
